<?php
/*
* Fetch: A PHP-friendly, flat-file, relational storage system.
* Copyright (C) 2007 Ben Ng
* 
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
* 
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
* 
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
* 
* VERSION: 0.9 BETA 3
*/

class fetch {
	/*
	* Important stuff
	* 
	* Not much to explain, except these are pretty important.
	*/
	var $fetch_version='0.9 Beta 3'; //This does not do anything
	var $file_data=array(); //Holds a structured array representing the table
	var $file_path=''; //Holds the text path to the table
	
	/*
	* Table container strings
	* 
	* If you change either of these, you will not be able to open table files that were made with different values.
	* That means, you will only be able to open tables created with these values.
	*/
	var $file_prepend_string='<?php die(\'Cannot view this file\'); ?>
'; //Holds a string that goes before a table file.
	var $file_append_string=''; //String that goes after a table file, but before the md5()
	
	/*Constructor for a table object, can accept the optional parameter $new which when set to true, creates the table if it does not exist*/
	function fetch($path, $new=false) {
		/*Variables*/
		$file; //The file is loaded in here as a string
		
		/*Test if the requested file does not exist*/
		if(!file_exists($path)) {
			/*Were we asked to create a new table?*/
			if($new===true)
			{
				/*If yes, try to create the file*/
				$file_handle=@fopen($path,'w');
				
				/*Test if the file was created*/
				if($file_handle===false || !file_exists($path)) {
					/*If the file failed to be created, triger an error*/
					trigger_error('New table could not be created',E_USER_ERROR);
				}
				else {
					/*Close the file handle*/
					fclose($file_handle);
					
					/*Write a blank table to the file*/
					$this->writeToFile($path,$this->generateEmptyDataArray());
				}
			}
			else {
				/*If not, trigger an error*/
				trigger_error('Table file does not exist at the specified location', E_USER_ERROR);
			}
		}
		/*Check if we were asked to create this table even though it already exists*/
		elseif($new===true) {
			trigger_error('Table already exists, cannot be created',E_USER_WARNING);
		}
		
		/*Save file path*/
		$this->file_path=$path;
		
		/*Get file data*/
		$this->file_data=$this->readFromFile($path);
	}
	
	/*Generates a default blank table structure*/
	function generateEmptyDataArray() {
		$table = array(
		'version'=>$this->pTable_version, //Version of this table
		'time_created'=>time(), //Time this table was created
		'last_modified'=>time(), //Last modified time
		'last_id'=>-1, //The last ID used (starts as -1 so that the first record is ID'ed as zero)
		'indexes'=>array(), //array($index=>$id), keeps track of each record's original ID even after some have been deleted
		'keys'=>array(), //array($id=>$data), the values entered into key fields are checked for uniqueness
		'fields'=>array(), //array($id=>$data), the values entered into normal fields have no restrictions on value
		'query_cache'=>array(), //array($query=>array('expires'=>$time,'result'=>$data))
		);
		
		return $table;
	}
	
	/*Saves the current state of the table using writeToFile*/
	function commit() {
		/*Flush out expired query results in the cache*/
		$this->flushQCache();
		return $this->writeToFile($this->file_path, $this->file_data);
	}
	
	/*Serializes mixed data and writes it to a file, appending an md5 hash to keep data secure*/
	function writeToFile($file_path, $data) {
		/*Test for writability*/
		if(!is_writable($file_path))
			trigger_error('File cannot be written to', E_USER_ERROR);
		
		/*Serialize mixed data into a string*/
		$file_string=serialize($data);
		
		/*Prepend and append strings*/
		$file_string=$this->file_prepend_string.$file_string.$this->file_append_string;
		
		/*Open and write specially formatted data*/
		$file_handle=fopen($file_path,'w');
		$status=@fwrite($file_handle,$file_string.md5($file_string));
		fclose($file_handle);
		
		/*Return the value of the fwrite() call*/
		return $status;
	}
	
	/*Checks for data integrity, then unserializes and returns mixed data stored using writeToFile*/
	function readFromFile($file_path) {
		/*Check if the file is readable*/
		if(!is_readable($file_path))
			trigger_error('File cannot be read from', E_USER_ERROR);
		
		/*Get file data*/
		$file_string=file_get_contents($file_path);
		
		/*Find crucial lengths*/
		$start_length=strlen($this->file_prepend_string);
		$end_length=strlen($this->file_append_string);
		$hash_length=32;
		
		/*For this file to be valid, it should be longer than an md5 hash*/
		/*The md5 hash appended should match up with the md5 of the data in the file before the appended hash*/
		if(strlen($file_string)<$hash_length+$start_length+$end_length || substr($file_string,-$hash_length)!=md5(substr($file_string,0,strlen($file_string)-$hash_length)))
			trigger_error('File is corrupt or invalid', E_USER_ERROR);
			
		/*If everything went well, unserialize the text before the md5 hash*/
		$dataString=substr($file_string,$start_length,strlen($file_string)-$hash_length-$end_length-$start_length);
		$read_file_data=unserialize($dataString);
		
		if($read_file_data===false)
			die('Table data could not be read from file: <tt>'.$dataString.'</tt>');
				
		/*Copy then convert indexes array into an ids array*/
		$read_file_data['ids']=array_flip($read_file_data['indexes']);
		
		return $read_file_data;
	}
	
	/*Creates a new field*/
	function addField($field_name, $is_key=false) {
		/*Convert the field name to lowercase and strip everything but digits, alphabet and the underscore*/
		$legal_name=preg_replace('[^0-9A-Za-z_.]','',$field_name);
		
		/*Check if the 'legal' name does not match the given name*/
		if($legal_name!==$field_name) {
			/*If so, trigger an error*/
			trigger_error('Field and key names may only contain letters, digits and the underscore',E_USER_ERROR);
			
			return false;
		}
		
		/*Check if the name is already used in an existing key or field*/
		if(!$this->isFieldOrKey($legal_name)) {	
			/*Now we see if we are creating a key*/
			if($is_key===true) {
				/*If this database has had data added before, we cannot allow a key to be made*/
				if($this->file_data['last_id']!==-1) {
					trigger_error('Keys can only be added to a new table');
				}
				/*If not, we can make this key*/
				else {
					$this->file_data['keys'][$legal_name]=array();
				}
			}
			/*If we are making a field then we can just go on ahead*/
			else {
				$this->file_data['fields'][$legal_name]=array();
			}
			
			return true;
		}
		else {
			/*If it is in use, trigger an error*/
			trigger_error('A field or key with this name already exists', E_USER_WARNING);
			
			return false;
		}
	}
	
	/*Creates a new key through the new field method*/
	function addKey($key_name) {
		return $this->addField($key_name, true);
	}
	
	/*Accessor method for checking if a key exists*/
	function isKey($name) {
		return array_key_exists($name, $this->file_data['keys']);
	}
	
	/*Accessor method for checking if a field exists*/
	function isField($name) {
		return array_key_exists($name, $this->file_data['fields']);
	}
	
	/*Accessor method for checking if a field or key exists*/
	function isFieldOrKey($field_name) {
		return $this->isKey($field_name) || $this->isField($field_name);
	}
	
	/*Accessor method returning an array of field names*/
	function getFields() {
		return array_keys($this->file_data['fields']);
	}
	
	/*Accessor method returning an array of key names*/
	function getKeys() {
		return array_keys($this->file_data['keys']);

	}
	
	/*Accessor method returning an array of field and key names*/
	function getKeysAndFields() {
		return array_merge($this->getFields(),$this->getKeys());
	}
	
	/*Adds a row to the table, inserting data for fields that exist and checking key data before insertion*/
	/*Returns the integer ID assigned to the new row or false on failure*/
	function addRow($data) {
		/*Bump the last_id up by one, to get our new ID*/
		$new_id=$this->file_data['last_id']+1;
		
		/*Loop through each required key*/
		foreach($this->getKeys() as $key_name)
		{
			/*Make sure that the value for this key is unique and not left empty*/
			if(isset($data[$key_name]) && array_search($data[$key_name],$this->file_data['keys'][$key_name])===false) {
				/*If the value is allowed, add it to the key data*/
				$this->file_data['keys'][$key_name][$new_id]=$data[$key_name];
			}
			else {					
				/*If not, trigger an error*/
				trigger_error('Insertion failed: value "'.$data[$key_name].'" is either missing or not unique for the key "'.$key_name.'"',E_USER_ERROR);
				
				return false;
			}
		}
		
		/*Loop through each optional field*/
		foreach($this->getFields() as $field_name)
		{
			if(isset($data[$field_name])) {
				/*If the value for this field was specified, add it to the field data*/
				$this->file_data['fields'][$field_name][$new_id]=$data[$field_name];
			}
		}
		
		/*Insert an index->ID reference*/
		$this->file_data['indexes'][]=$new_id;
		
		/*Bump up the last_id*/
		$this->file_data['last_id']++;
		
		return $new_id;
	}
	
	/*Removes a row from the table*/
	function dropRow($row_id) {
		/*Test if the ID exists*/
		if($this->idExists($row_id))
		{
			/*If yes, loop through all the keys and fields and remove all values with that ID*/
			foreach($this->getKeys() as $key_name)
				unset($this->file_data['keys'][$key_name][$row_id]);
			foreach($this->getFields() as $field_name)
				unset($this->file_data['fields'][$field_name][$row_id]);
				
			/*Remove the index reference by searching for the value and using the returned key to unset the record*/
			unset($this->file_data['indexes'][array_search($row_id,$this->file_data['indexes'])]);
			
			/*Resort the index references*/
			sort($this->file_data['indexes']);
			
			return true;
		}
		else
		{
			/*If the ID does not exist, trigger a warning*/
			trigger_error('The row does not exist and cannot be dropped', E_USER_WARNING);
		}
	}
	
	/*Replaces or inserts new values into a row*/
	function updateRow($row_id, $data) {
		/*Test if the ID exists*/
		if($this->idExists($row_id))
		{
			/*Loop through each required key*/
			foreach($this->getKeys() as $key_name)
			{
				/*Check if a new value was provided for this key*/
				if(isset($data[$key_name]))
				{
					/*If a new value is provided, check that the value for this key is unique*/
					$foundKey=array_search($data[$key_name],$this->file_data['keys'][$key_name]);
					/*Now check if the key was not found, or if it was found on the saw row we are editing (so there is no change)*/
					if($foundKey===false || $foundKey===$row_id) {
						/*If the value is allowed, add it to the key data*/
						$this->file_data['keys'][$key_name][$row_id]=$data[$key_name];
					}
					else {					
						/*If not, trigger an error*/
						trigger_error('Insertion failed: value is either missing or not unique for the key "'.$key_name.'"',E_USER_ERROR);
						
						return false;
					}
				}
			}
			
			/*Loop through each optional field*/
			foreach($this->getFields() as $field_name)
			{
				if(isset($data[$field_name])) {
					/*If the value for this field was specified, add it to the field data*/
					$this->file_data['fields'][$field_name][$row_id]=$data[$field_name];
				}
			}
		}
		else
		{
			/*If the ID does not exist, trigger a warning*/
			trigger_error('The row does not exist and cannot be updated', E_USER_WARNING);
		}
	}
	
	/*Checks if an ID exists*/
	function idExists($id) {
		return array_search($id,$this->file_data['indexes'])===false?false:true;
	}
	
	/*Returns the index of an id*/
	function idToIndex($id) {
		return array_search($id,$this->file_data['indexes']);
	}
	
	/*Returns the id of an index*/
	function indexToId($id) {
		if(isset($this->file_data['indexes'][$index]))
			return $this->file_data['indexes'][$index];
		else
			return false;
	}
	
	/*Returns the number of rows in the table*/
	function countRows() {
		return count($this->file_data['indexes']);
	}
	
	/*Returns the filesize of the table*/
	function size() {
		/*Prepend and append strings to serialized data and return length*/
		return strlen($this->file_prepend_string.serialize($this->file_data).$this->file_append_string);
	}
	
	/*Returns a multi-dimensional array of results in the format $results[$rec_id][$field_name]=$value*/
	function select($sourceQuery,$fields=false, $expires_in=0) {
		/*Check if the query is already in the cache, and if it hasn't expired*/
		if(array_key_exists($sourceQuery,$this->file_data['query_cache']) && $this->file_data['query_cache'][$sourceQuery]['expires']>time()) {
			return $this->file_data['query_cache'][$sourceQuery]['result'];
		}
		$keysToGet=array();
		$fieldsToGet=array();
		/*If no fields were specified, load up everything*/
		if($fields===false || $fields=='*' || count($fields)<=0) {
			$keysToGet=$this->getKeys();
			$fieldsToGet=$this->getFields();
		}
		else {
		/*If not, seperate wanted fields into keys and fields*/
			if(count($fields)>0) {
				foreach($fields as $fieldname) {
				if($this->isKey($fieldname))
					$keysToGet[]=$fieldname;
				elseif($this->isField($fieldname))
					$fieldsToGet[]=$fieldname;
				else
					trigger_error('Query failed, the field or key "'.$fieldname.'" does not exist',E_USER_ERROR);
				}
			}
			else {
				$fields=array();
			}
		}
		
		$query=$this->queryToClause($sourceQuery);
		/*Now, plug in the conditional into our premade query script*/
		$query_code='
		$id_range=array_values($this->file_data[\'indexes\']);
		$results=array();
		foreach($id_range as $row_id)
		{
			if('.$query.') {';
			//Add code to add key values
			if(count($keysToGet)>0)
				foreach($keysToGet as $get_key_name)
					$query_code.='
					$results[$row_id][\''.$get_key_name.'\']=$this->file_data[\'keys\'][\''.$get_key_name.'\'][$row_id];';
			//Add code to add field values
			if(count($fieldsToGet)>0)
				foreach($fieldsToGet as $get_field_name)
					$query_code.='
					$results[$row_id][\''.$get_field_name.'\']=$this->file_data[\'fields\'][\''.$get_field_name.'\'][$row_id];';
			//If there were no wanted fields, add an empty array
			if($fields===NULL||count($fields)==0)
					$query_code.='
					$results[$row_id]=array();';
		$query_code.='
			}
		}
		';
		
		/*Run query (whee)*/
		eval($query_code);
		
		/*Pop query into cache if we need to*/
		if($expires_in>0) {
			$this->file_data['query_cache'][$sourceQuery]=array('expires'=>time()+$expires_in,'result'=>$results);
		}
		
		return $results;
	}
	
	/*Removes expired queries from the cache*/
	function flushQCache() {
		foreach($this->file_data['query_cache'] as $query => $data)
			if(time()>$data['expires'])
				unset($this->file_data['query_cache'][$query]);
	}
	
	/*Returns the first row ID that matches the parameters*/
	function firstMatchingId($query) {
		$query=$this->queryToClause($query);
		$result=false;
		/*Now, plug in the conditional into our premade query script*/
		$query_code='
		$id_range=array_values($this->file_data[\'indexes\']);
		foreach($id_range as $row_id)
		{
			if('.$query.') {
			$result=$row_id;
			break;
			}
		}
		';
		
		/*Run query (whee)*/
		eval($query_code);
		return $result;
	}
	
	/*Turns a query string into a clause*/
	function queryToClause($query) {
		/*First, we identify special fields like ID and KEY betwen double %% and replace them with their special pointers*/
		$query=str_replace(array('%%ID%%','%%INDEX%%'),array('$row_id','$this->file_data[\'ids\'][$row_id]'),$query);
		
		/*Then, identify field and names between %% and replace them with their pointers*/
		foreach($this->getKeys() as $key_name) {
			$query=str_replace('%'.$key_name.'%','$this->file_data[\'keys\'][\''.$key_name.'\'][$row_id]',$query);
		}
		foreach($this->getFields() as $field_name) {
			$query=str_replace('%'.$field_name.'%','$this->file_data[\'fields\'][\''.$field_name.'\'][$row_id]',$query);
		}
		
		/*Next, replace all instances of escaped %'s with single %s*/
		$query=str_replace('\\%','%',$query);
		return $query;
	}
	
	/*Returns the value of a field from a row id*/
	function getFieldValue($row_id, $field_name) {
		if(!$this->idExists($row_id)) {
			trigger_error('The id does not exist',E_USER_ERROR);
		}
		
		if($this->isField($field_name)) {
			/*If the field is a field*/
			return $this->file_data['fields'][$field_name][$row_id];
		}
		elseif($this->isKey($field_name)) {
			/*If the field is a key*/
			return $this->file_data['keys'][$field_name][$row_id];
		}
		else {
			/*If the field didn't trigger our checks above, it dosen't exist and trigger an error*/
			trigger_error('Failed to get value, the field or key "'.$field_name.'" does not exist', E_USER_ERROR);
		}
	}
	
	/*Escapes a string for database entry BETWEEN SINGLE QUOTES!!!*/
	function qEscape($string) {
		return str_replace(array('%','\\','\''),array('\\%','\\\\','\\\''),$string);
	}
	
	/*Unescapes then escapes a recieved post variable for database entry*/
	function qEscapePostVar($string) {
		return $this->qEscape($this->unescapePostVar($string),$string);
	}
	
	/*Escapes a recieved post variable*/
	function unescapePostVar($string) {
		return str_replace(array('\\\\','\\"','\\\''),array('\\','"','\''),$string);
	}
	
	/*Sorts a selection by field*/
	function sortSelection($selection, $field, $sort_type=SORT_ASC, $sort_flags=SORT_REGULAR) {
		if(count($selection)<=1)
			/*If there is one or less records, theres no point in sorting*/
			return $selection;
		if(array_key_exists($field, array_pop(array_values($selection)))===false)
			/*If the field does not exist there is no way to sort, so trigger an error*/
			trigger_error('Cannot sort selection, field "'.$field.'" does not exist', E_USER_ERROR);
		
		/*First, we put all the sorting values into an array keyed by the record id it came from*/
		$sort_arr=array();
		foreach($selection as $rec_id => $values) {
			$sort_arr[$rec_id]=$values[$field];
		}
		
		/*Next we sort the array, preserving key values*/
		if($sort_type==SORT_DESC)
			arsort($sort_arr,$sort_flags);
		else
			asort($sort_arr,$sort_flags);
		
		/*Finally, build a new array with the sorted indexes*/
		$new_arr=array();
		foreach($sort_arr as $rec_id => $values) {
			$new_arr[$rec_id]=$selection[$rec_id];
		}
		
		return $new_arr;
	}

	/*Prints out an ascii representation of a selection*/
	function selectionToAscii($selection) {
		$longest_value=0;
		$fields_in_selection=array_keys(array_pop(array_values($selection)));
		$output='';
		
		/*Set up correct id range*/
		$id_range=array_keys($selection);
		
		/*Find longest value*/
		foreach($fields_in_selection as $field_name) {
			foreach($id_range as $row_id)
			{
				if(strlen($selection[$row_id][$field_name])>$longest_value) {
					$longest_value=strlen($selection[$row_id][$field_name]);
				}
			}
			if(strlen($field_name)>$longest_value) {
				$longest_value=strlen($field_name);
			}
		}
		
		/*Build header/footer*/
		$header='+ ';
		foreach($fields_in_selection as $field_name) {
			$header.=str_pad($field_name,$longest_value+1,' ',STR_PAD_RIGHT).'+ ';
		}
		$output.=($header."\n");
		
		/*Print content*/
		foreach($id_range as $row_id)
		{
			$output.=('| ');
			foreach($fields_in_selection as $field_name)
				$output.=str_pad($selection[$row_id][$field_name],$longest_value+1,' ',STR_PAD_RIGHT).'| ';
				
			$output.=("\n");
		}
		
		$output.=($header);
		return $output;
	}
}

class temptable extends fetch {
	function commit($save_path=false) {
		//Makes sure the file path is specified
		if($this->file_path==false && $save_path==false)
			trigger_error('Must specify a file path the first time when committing a temporary join table',E_USER_ERROR);
		else
			$this->file_path=$save_path;

		/*Flush out expired query results in the cache*/
		$this->flushQCache();
		
		return $this->writeToFile($this->file_path, $this->file_data);
	}
	//Add all fields to table, adding prefixes to them then returning the new selection
	function addAllFields(&$selection, $prefix) {
		//Add the fields from the first selection
		foreach(array_pop(array_values($selection)) as $fieldname => $junkdata)
			$this->addField($prefix.$fieldname);
		//Add prefix to field names
		$newselection=array(array());
		foreach($selection as $row_id=>$data)
			foreach($data as $fieldname => $data)
				$newselection[$row_id][$prefix.$fieldname]=$data;

		return $newselection;
	}
	function queryToSelectClause($query) {
		/*First, identify field and names between %% and replace them with their pointers*/
		foreach($this->getKeysAndFields() as $field_name) {
			if(substr($field_name,0,2)=='a.')
				$query=str_replace('%'.$field_name.'%','$data_1[\''.$field_name.'\']',$query);
			else
				$query=str_replace('%'.$field_name.'%','$data_2[\''.$field_name.'\']',$query);
		}
		
		/*Next, replace all instances of escaped %'s with single %s*/
		$query=str_replace('\\%','%',$query);
		
		return $query;
	}
	function writeToFile($file_path, $data) {trigger_error('Cannot write to a temporary join table',E_USER_ERROR);}
	function readFromFile($file_path) {trigger_error('Cannot read from a temporary join table',E_USER_ERROR);}
}

class innerjoin extends temptable {
	function innerjoin($selection1, $selection2, $condition) {
		$this->file_data=$this->generateEmptyDataArray();
		$selection1=$this->addAllFields($selection1,'a.');
		$selection2=$this->addAllFields($selection2,'b.');

		//Approve records by matching up the selected field
		$code='
		foreach($selection1 as $id_no_1=>$data_1)
			foreach($selection2 as $id_no_2=>$data_2)
				if('.$this->queryToSelectClause($condition).') {
					$this->addRow(array_merge($data_1, $data_2));
					break;
				}
		';
		eval($code);
	}
}

class crossjoin extends temptable {
	function crossjoin($selection1, $selection2) {
		$this->file_data=$this->generateEmptyDataArray();
		$selection1=$this->addAllFields($selection1,'a.');
		$selection2=$this->addAllFields($selection2,'b.');

		//Add everything
		$code='
		foreach($selection1 as $id_no_1=>$data_1)
			foreach($selection2 as $id_no_2=>$data_2)
				$this->addRow(array_merge($data_1, $data_2));
		';
		eval($code);
	}
}

class left_outerjoin extends temptable {
	function left_outerjoin($selection1, $selection2, $condition) {
		$this->file_data=$this->generateEmptyDataArray();
		$selection1=$this->addAllFields($selection1,'a.');
		$selection2=$this->addAllFields($selection2,'b.');
		//Create null array of data for array 2
		$null_data_2=array();
		foreach(array_pop(array_values($selection2)) as $fieldname => $junk)
			$null_data_2[$fieldname]=NULL;
		//Add everything
		$code='
		foreach($selection1 as $id_no_1=>$data_1) {
			$found=false;
			foreach($selection2 as $id_no_2=>$data_2) {
				if('.$this->queryToSelectClause($condition).') {
					$this->addRow(array_merge($data_1, $data_2));
					$found=true;
				}
			}
			if($found===false)
				$this->addRow(array_merge($data_1, $null_data_2));
		}
		';
		eval($code);
	}
}

class right_outerjoin extends temptable {
	function right_outerjoin($selection1, $selection2, $condition) {
		$this->file_data=$this->generateEmptyDataArray();
		$selection1=$this->addAllFields($selection1,'a.');
		$selection2=$this->addAllFields($selection2,'b.');
		//Create null array of data for array 2
		$null_data_1=array();
		foreach(array_pop(array_values($selection1)) as $fieldname => $junk)
			$null_data_1[$fieldname]=NULL;
		//Add everything
		$code='
		foreach($selection2 as $id_no_2=>$data_2) {
			$found=false;
				foreach($selection1 as $id_no_1=>$data_1) {
				if('.$this->queryToSelectClause($condition).') {
					$this->addRow(array_merge($data_1, $data_2));
					$found=true;
				}
			}
			if($found===false)
				$this->addRow(array_merge($null_data_1, $data_2));
		}
		';
		eval($code);
	}
}
?>